package com.zzedu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.zzedu.domain.ZzPictureBook;
import com.zzedu.dto.QueryZzPictureBookDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

/**
 * 课程管理Mapper接口
 *
 * @author Mr.Wang
 * @date 2025-04-04
 */
public interface ZzPictureBookMapper extends BaseMapper<ZzPictureBook> {

    @Select({
            "<script>",
            "SELECT a.*",
            "FROM zz_picture_book a",
            "LEFT JOIN zz_course b ON a.unit_id = b.id",
            "WHERE a.deleted = 0",
            "  AND b.deleted = 0",
            "  AND b.id IN",
            "  <foreach item='id' collection='unitIdList' open='(' separator=',' close=')'>",
            "    #{id}",
            "  </foreach>",
            "ORDER BY b.sort_no ASC,a.id ASC, b.sort_no ASC,a.id  ASC", // 保留两个排序字段（如需去重见下方说明）
            "</script>"
    })
    List<ZzPictureBook> getZzPictureBookByUnitList(@Param("unitIdList") List<Long> unitIdList);


    @Select({
            "<script>",
            "SELECT  b.*, a.clzz_id,a.study_date,a.start_date,a.end_date ",
            "FROM zz_timetable a",
            "LEFT JOIN zz_picture_book b ON a.book_id = b.id",
            "WHERE ",
            "  b.unit_id in(select id from zz_course where deleted=0)",
            "  AND a.deleted = 0",
            "  AND b.deleted = 0",
            "<if test='unitId != null and unitId != \"\"'> AND b.unit_id = #{unitId} </if>",
            "  AND EXISTS (",
            "    SELECT 1 FROM zz_classes c",
            "    WHERE c.id = a.clzz_id",
            "      AND EXISTS (select 1 from zz_stu_classes_r d where c.id=d.class_id and  d.deleted=0 and d.stu_id=#{stuId})",
            "      AND c.deleted = 0",
            "      AND c.school_id = #{schoolId}",
            "  ) ",
            "  AND a.book_id>0 and b.show_model in(1,3)",
            "  order by a.study_date asc",
            "</script>"
    })
    IPage<QueryZzPictureBookDTO> apiQueryZzPictureBookPage(IPage<QueryZzPictureBookDTO> page, @Param("stuId") Long stuId, @Param("schoolId") Long schoolId, @Param("unitId") Long unitId);


    @Select({
            "<script>",
            "SELECT a.*",
            "FROM zz_picture_book a",
            "LEFT JOIN zz_course b ON a.unit_id = b.id",
            "WHERE a.deleted = 0",
            "  AND b.deleted = 0",
            "  AND a.id IN (",
            "      SELECT book_id",
            "      FROM zz_timetable",
            "      WHERE deleted = 0",
            "        AND school_id = #{schoolId}",
            "        AND clzz_id = #{clzzId}",
            "        AND study_date &lt; #{studyDate}",
            "  )",
            "ORDER BY b.sort_no ASC, a.id ASC",
            "</script>"
    })
    @Deprecated
    IPage<ZzPictureBook> apiClassQueryZzPictureBookPage(
            IPage<ZzPictureBook> page,
            @Param("schoolId") Long schoolId,
            @Param("clzzId") Long clzzId,
            @Param("studyDate") LocalDateTime now
    );


    @Select({
            "<script>",
            "SELECT c.*,a.un_lock_date",
            "FROM zz_classes_send_student a",
            "INNER JOIN zz_course b ON b.id = a.unit_id",
            "INNER JOIN zz_picture_book c ON c.id = a.book_id",
            "WHERE a.deleted = 0",
            "  AND b.deleted = 0",
            "  AND c.deleted = 0",
            "  <if test='unitId != null'>AND a.unit_id = #{unitId}</if>",
            "  <if test='stuId != null'>AND a.stu_id = #{stuId}</if>",
            "  <if test='schoolId != null'>AND a.school_id = #{schoolId}</if>",
            "ORDER BY \tb.sort_no,c.sort_no,a.id",
            "</script>"
    })
    /**
     * 按照 作业发送逻辑查询绘本列表
     * @param page
     * @param stuId
     * @param schoolId
     * @param unitId
     * @return
     */
    IPage<ZzPictureBook> apiQueryZzPictureBookPageNews(IPage<QueryZzPictureBookDTO> page, @Param("stuId") Long stuId, @Param("schoolId") Long schoolId, @Param("unitId") Long unitId);


    @Select({
            "<script>",
            "SELECT c.*,a.id as sortId",
            "FROM zz_school_course a",
            "INNER JOIN zz_course b ON a.unit_id = b.id",
            "INNER JOIN zz_picture_book c ON c.unit_id = a.unit_id",
            "WHERE a.deleted = 0",
            "  AND b.deleted = 0",
            "  AND b.state = 0",
            "  AND c.deleted = 0",
            "  and c.show_model in(2,3)",
            "  <if test='schoolId != null'>AND a.school_id = #{schoolId}</if>",
            "  <if test='level != null and level != \"\"'>AND b.level = #{level}</if>",
            "  <if test='unitId != null'>AND a.unit_id = #{unitId}</if>",
            "ORDER BY b.sort_no ASC, c.sort_no ASC, a.id ASC",
            "</script>"
    })
    /**
     * 大屏#查询所有绘本列表
     * @param page
     * @param stuId
     * @param schoolId
     * @param unitId
     * @return
     */
    IPage<ZzPictureBook> apiDeviceZzPictureBookQueryPageNews(IPage<ZzPictureBook> page, @Param("level") String level, @Param("schoolId") Long schoolId, @Param("unitId") Long unitId, @Param("stateList") List<Integer> stateList);


    @Select({
            "<script>",
            "SELECT c.*,a.id as sortId",
            "FROM zz_school_course a",
            "INNER JOIN zz_course b ON a.unit_id = b.id",
            "INNER JOIN zz_picture_book c ON c.unit_id = a.unit_id",
            "WHERE a.deleted = 0",
            "  AND b.state = 0",
            "  AND b.deleted = 0",
            "  AND c.deleted = 0",
            "  and c.show_model in(1,3)",
            "  <if test='schoolId != null'>AND a.school_id = #{schoolId}</if>",
            "  <if test='level != null and level != \"\"'>AND b.level = #{level}</if>",
            "  <if test='unitId != null'>AND a.unit_id = #{unitId}</if>",
            "ORDER BY b.sort_no ASC, c.sort_no ASC, a.id ASC",
            "</script>"
    })
    /**
     * 小程序#查询所有绘本列表
     * @param page
     * @param stuId
     * @param schoolId
     * @param unitId
     * @return
     */
    IPage<ZzPictureBook> apiMiniProZzPictureBookQueryPageNews(IPage<ZzPictureBook> page, @Param("level") String level, @Param("schoolId") Long schoolId, @Param("unitId") Long unitId, @Param("stateList") List<Integer> stateList);

}
